package cn.sunxyz.jdbc;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Created by yangrd on 2017/5/31.
 */
public class JdbcBatchHandleByStatement {


    private static final Logger logger = LoggerFactory.getLogger(JdbcBatchHandleByStatement.class);
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    @Before
    public void before() throws Exception {
        //获取一个数据库连接
        conn = JdbcUtils.getConnection();
        logger.debug("connection : {}", conn);
    }

    @After
    public void after() throws Exception {
        logger.debug("release : {} {} {}", conn, st, rs);
        JdbcUtils.release(conn, st, rs);
    }

    /**
     * 采用Statement.addBatch(sql)方式实现批处理的优缺点
     * <p>
     * 　　采用Statement.addBatch(sql)方式实现批处理：
     * 　　　　优点：可以向数据库发送多条不同的ＳＱＬ语句。
     * 　　　　缺点：SQL语句没有预编译。
     * 　　　　当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：
     * 1 Insert into user(name,password) values('aa','111');
     * 2 Insert into user(name,password) values('bb','222');
     * 3 Insert into user(name,password) values('cc','333');
     * 4 Insert into user(name,password) values('dd','444');
     */
    @Test
    public void test() {
        try {
            String sql1 = "insert into testbatch(id,name) values(1,'aaa')";
            String sql2 = "insert into testbatch(id,name) values(2,'bbb')";
            String sql3 = "insert into testbatch(id,name) values(3,'CCC')";
            String sql4 = "insert into testbatch(id,name) values(4,'DDD')";
            String sql5 = "update testbatch set name='gacl' where id=1";
            String sql6 = "insert into testbatch(id,name) values(5,'FFF')";
            String sql7 = "delete from testbatch where id=2";
            st = conn.createStatement();
            //添加要批量执行的SQL
            st.addBatch(sql1);
            st.addBatch(sql2);
            st.addBatch(sql3);
            st.addBatch(sql4);
            st.addBatch(sql5);
            st.addBatch(sql6);
            st.addBatch(sql7);
            //执行批处理SQL语句
            st.executeBatch();
            //清除批处理命令
            st.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
